﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Dapper;
using BCEntity.Common.DeviceInfo;
using BCCommon;
using System.Threading.Tasks;

namespace BCData.Common.DeviceInfo
{
    public class DeviceInfoData : IDeviceInfoData
    {
        private const string SQL_INSERT_ITEM = @"INSERT INTO DeviceInfo (UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime)
                                                 VALUES (?UserId, ?AppType, ?OSType, ?OSVersion, ?Model, ?DeviceId, ?PushType, ?PushToken, ?QuietMode, ?CreateTime, ?UpdateTime);
                                                 SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime
                                                 FROM DeviceInfo
                                                 WHERE DeviceInfoId = LAST_INSERT_ID()";
        private const string SQL_UPDATE_ITEM_BY_DEVICEINFOID = @"UPDATE DeviceInfo
                                                                 SET UserId = ?UserId, AppType = ?AppType, OSType = ?OSType, OSVersion = ?OSVersion, Model = ?Model, DeviceId = ?DeviceId, PushType = ?PushType, PushToken = ?PushToken, QuietMode = ?QuietMode, CreateTime = ?CreateTime, UpdateTime = ?UpdateTime
                                                                 WHERE DeviceInfoId = ?DeviceInfoId;
                                                                 SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime
                                                                 FROM DeviceInfo
                                                                 WHERE DeviceInfoId = ?DeviceInfoId";
        private const string SQL_SELECT_ITEMS_BY_APPTYPE = @"SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime
                                                            FROM DeviceInfo
                                                            WHERE AppType = ?AppType";
        private const string SQL_QUERY_USERID = @"SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime FROM DeviceInfo";
        private const string SQL_SELECT_TOKENS = @"SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime FROM DeviceInfo WHERE QuietMode = 0 AND AppType = ?AppType AND UserId = ?UserId AND UpdateTime > ?UpdateTime";
        private const string SQL_SELECTS_TOKENS = @"SELECT DeviceInfoId, UserId, AppType, OSType, OSVersion, Model, DeviceId, PushType, PushToken, QuietMode, CreateTime, UpdateTime FROM DeviceInfo WHERE QuietMode = 0 AND AppType = ?AppType AND UserId IN ?UserId AND UpdateTime > ?UpdateTime";
        private const string SQL_SELECT_ITEMS_DETAIL_VIEW = @"SELECT D.DeviceInfoId,D.UserId,D.AppType,D.OSType,D.OSVersion,D.Model,D.DeviceId,D.PushType,D.PushToken,D.QuietMode,D.CreateTime,D.UpdateTime,
                                                              U.Phone,U.UserName,U.Role FROM deviceinfo D JOIN `User` U ON D.UserId = U.UserId ";

        private readonly IDatabaseContext database;

        public DeviceInfoData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<DeviceInfoEntity> Create(DeviceInfoEntity entity)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?UserId", entity.UserId, DbType.Int64);
            parameters.Add("?AppType", entity.AppType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?OSType", entity.OSType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?OSVersion", entity.OSVersion, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?Model", entity.Model, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?DeviceId", entity.DeviceId, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?PushType", entity.PushType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?PushToken", entity.PushToken, DbType.AnsiString, ParameterDirection.Input, 255);
            parameters.Add("?QuietMode", entity.QuietMode, DbType.Boolean);
            parameters.Add("?CreateTime", entity.CreateTime, DbType.DateTime);
            parameters.Add("?UpdateTime", entity.UpdateTime, DbType.DateTime);

            return await database.Slave.QueryFirstOrDefaultAsync<DeviceInfoEntity>(SQL_INSERT_ITEM, parameters);
        }

        public void UpdateAsync(DeviceInfoEntity entity)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?UserId", entity.UserId, DbType.Int64);
            parameters.Add("?AppType", entity.AppType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?OSType", entity.OSType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?OSVersion", entity.OSVersion, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?Model", entity.Model, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?DeviceId", entity.DeviceId, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?PushType", entity.PushType, DbType.AnsiString, ParameterDirection.Input, 50);
            parameters.Add("?PushToken", entity.PushToken, DbType.AnsiString, ParameterDirection.Input, 255);
            parameters.Add("?QuietMode", entity.QuietMode, DbType.Boolean);
            parameters.Add("?CreateTime", entity.CreateTime, DbType.DateTime);
            parameters.Add("?UpdateTime", entity.UpdateTime, DbType.DateTime);
            parameters.Add("?DeviceInfoId", entity.DeviceInfoId, DbType.Int64);

            database.Master.ExecuteAsync(SQL_UPDATE_ITEM_BY_DEVICEINFOID, parameters);
        }

        public async Task<IEnumerable<DeviceInfoEntity>> GetPushTokens(string appType, long userId, int days)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?AppType", appType, DbType.AnsiString);
            parameters.Add("?UserId", userId, DbType.Int64);
            parameters.Add("?UpdateTime", DateTime.Now.AddDays(-days), DbType.DateTime);

            return await database.Slave.QueryAsync<DeviceInfoEntity>(SQL_SELECT_TOKENS, parameters);
        }
        public async Task<IEnumerable<DeviceInfoEntity>> GetPushTokens(string appType, long[] userIds, int days)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?AppType", appType, DbType.AnsiString);
            parameters.Add("?UserId", userIds, null);
            parameters.Add("?UpdateTime", DateTime.Now.AddDays(-days), DbType.DateTime);

            return await database.Slave.QueryAsync<DeviceInfoEntity>(SQL_SELECTS_TOKENS, parameters);
        }

        public async Task<IEnumerable<DeviceInfoEntity>> GetDeviceInfosByAppTypeAndDeviceId(string appType, string osType, string model, string deviceId)
        {
            DynamicParameters parameters = new DynamicParameters();

            string sql = SQL_SELECT_ITEMS_BY_APPTYPE;

            parameters.Add("?AppType", appType, DbType.AnsiString);

            if (!string.IsNullOrWhiteSpace(osType))
            {
                sql += " AND OSType = ?OSType";
                parameters.Add("?OSType", osType, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(model))
            {
                sql += " AND Model = ?Model";
                parameters.Add("?Model", model, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(deviceId))
            {
                sql += " AND DeviceId = ?DeviceId";
                parameters.Add("?DeviceId", deviceId, DbType.AnsiString);
            }

            return await database.Slave.QueryAsync<DeviceInfoEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<DeviceInfoViewEntity>, int)> GetDeviceInfoViews(string phone, string userName, string role, string appType, string osType, string model, string deviceId, bool? ascending, int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();

            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add(" U.Phone LIKE CONCAT('%',CONCAT(?Phone,'%'))");
                parameters.Add("?Phone", phone, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(userName))
            {
                wheres.Add(" U.UserName LIKE CONCAT('%',CONCAT(?UserName,'%'))");
                parameters.Add("?UserName", userName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(role))
            {
                wheres.Add("U.Role = ?Role");
                parameters.Add("?Role", role, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(appType))
            {
                wheres.Add("D.AppType = ?AppType");
                parameters.Add("?AppType", appType, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(osType))
            {
                wheres.Add("D.OSType = ?OSType");
                parameters.Add("?OSType", osType, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(model))
            {
                wheres.Add("D.Model = ?Model");
                parameters.Add("?Model", model, DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(deviceId))
            {
                wheres.Add("D.DeviceId = ?DeviceId");
                parameters.Add("?DeviceId", deviceId, DbType.AnsiString);
            }

            string sql = string.Format(@"{0} {1} ORDER BY D.CreateTime {4} LIMIT {2},{3};
                               SELECT Count(1) FROM deviceinfo D JOIN `User` U ON D.UserId = U.UserId {1}",
                     SQL_SELECT_ITEMS_DETAIL_VIEW,
                     wheres.Count > 0 ? string.Format(" WHERE {0}", string.Join(" AND ", wheres)) : string.Empty,
                     (pageIndex - 1) * pageSize,
                     pageSize,
                     ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await database.Slave.QueryPageableListAsync<DeviceInfoViewEntity>(sql, parameters);
        }

        public async Task<DeviceInfoEntity> Get(long userId)
        {
            string sql = string.Format("{0} WHERE UserId=?UserId", SQL_QUERY_USERID);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await database.Slave.QueryFirstOrDefaultAsync<DeviceInfoEntity>(sql, parameters);
        }
    }
}
